﻿using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using TSqlServer;

public partial class system_zmag_list_xls : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if(!IsPostBack)
        {
            string magname = Request["magname"].ToString();
            string maglvl = Request["maglvl"].ToString();
            string magdept = Request["magdept"].ToString();
            string magcity = Request["magcity"].ToString();
            MakeXls(magname, maglvl, magdept,magcity);        
        }
    }

    private void MakeXls(string magname,string maglvl,string magdept,string magcity)
    {
        string wwwpath = Server.MapPath("..");

        string FileFrom = wwwpath + "\\template\\xls\\postlist.xls";
        string PathTo = wwwpath + "\\makefiles\\";
        //生成文件名,当前日期时间
        string myfilename = DateTime.Now.ToString("yyyyMMddHHmmssff")+".xls";
        string FileTo = PathTo + myfilename;
        //拷贝模板文件
        System.IO.File.Copy(FileFrom, FileTo);
        //---
        string cdate = DateTime.Today.ToString("yyyy-M-d");
        string myrowset = "insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;HDR=YES;DATABASE=" + FileTo + "',sheet1$) ";
        //string sSql = "select linkman as [name],company,companyaddr as address,companypostcode as postcode,giveqty as qty,givelvl as [level],dbo.getbignamebyid(inman) as postman from vw_givelist where status=4 and giveedate>='" + cdate + "' and givemag like '%" + magname + "%' and givelvl='" + maglvl + "' and uid in (select uid from vw_linkman_key2) order by company,companyaddr,linkman";
        string sSql = "";
        string scity = magcity;
        if (magdept == "全部")
        {
            switch (scity)
            {
                case "全部":
                    sSql = "select linkman,company,companyaddr,companypostcode,giveqty,givelvl,dbo.getbignamebyid(inman) as giveman from vw_givelist where status=4 and giveedate>='" + cdate + "' and givemag like '%" + magname + "%' and givelvl='" + maglvl + "' and uid in (select uid from vw_linkman_key2) order by linkman,company,companyaddr";
                    break;
                case "北京":
                    sSql = "select linkman,company,companyaddr,companypostcode,giveqty,givelvl,dbo.getbignamebyid(inman) as giveman from vw_givelist where status=4 and giveedate>='" + cdate + "' and givemag like '%" + magname + "%' and givelvl='" + maglvl + "' and uid in (select uid from vw_linkman_key2) and left(companypostcode,2)='10' order by linkman,company,companyaddr";
                    break;
                case "非北京":
                    sSql = "select linkman,company,companyaddr,companypostcode,giveqty,givelvl,dbo.getbignamebyid(inman) as giveman from vw_givelist where status=4 and giveedate>='" + cdate + "' and givemag like '%" + magname + "%' and givelvl='" + maglvl + "' and uid in (select uid from vw_linkman_key2) and left(companypostcode,2)<>'10' order by linkman,company,companyaddr";
                    break;
                default:
                    sSql = "select linkman,company,companyaddr,companypostcode,giveqty,givelvl,dbo.getbignamebyid(inman) as giveman from vw_givelist where status=4 and giveedate>='" + cdate + "' and givemag like '%" + magname + "%' and givelvl='" + maglvl + "' and uid in (select uid from vw_linkman_key2) order by linkman,company,companyaddr";
                    break;
            }
        }
        else
        {
            string stempdept = magdept;
            char[] parschar ={ '=' };
            string[] depts = stempdept.Split(parschar);

            //sSql = "select linkman,company,companyaddr,companypostcode,giveqty,givelvl,dbo.getbignamebyid(inman) as giveman from vw_givelist where status=4 and giveedate>='" + cdate + "' and givemag like '%" + magname + "%' and givelvl='" + maglvl + "' and uid in (select uid from vw_linkman_key2) and inman in (select uid from t_user where mycompany='" + depts[0] + "' and mydept='" + depts[1] + "') order by company,companyaddr,linkman";
            switch (scity)
            {
                case "全部":
                    sSql = "select linkman,company,companyaddr,companypostcode,giveqty,givelvl,dbo.getbignamebyid(inman) as giveman from vw_givelist where status=4 and giveedate>='" + cdate + "' and givemag like '%" + magname + "%' and givelvl='" + maglvl + "' and uid in (select uid from vw_linkman_key2) and inman in (select uid from t_user where mycompany='" + depts[0] + "' and mydept='" + depts[1] + "') order by linkman,company,companyaddr";
                    break;
                case "北京":
                    sSql = "select linkman,company,companyaddr,companypostcode,giveqty,givelvl,dbo.getbignamebyid(inman) as giveman from vw_givelist where status=4 and giveedate>='" + cdate + "' and givemag like '%" + magname + "%' and givelvl='" + maglvl + "' and uid in (select uid from vw_linkman_key2) and inman in (select uid from t_user where mycompany='" + depts[0] + "' and mydept='" + depts[1] + "') and left(companypostcode,2)='10' order by linkman,company,companyaddr";
                    break;
                case "非北京":
                    sSql = "select linkman,company,companyaddr,companypostcode,giveqty,givelvl,dbo.getbignamebyid(inman) as giveman from vw_givelist where status=4 and giveedate>='" + cdate + "' and givemag like '%" + magname + "%' and givelvl='" + maglvl + "' and uid in (select uid from vw_linkman_key2) and inman in (select uid from t_user where mycompany='" + depts[0] + "' and mydept='" + depts[1] + "') and left(companypostcode,2)<>'10' order by linkman,company,companyaddr";
                    break;
                default:
                    sSql = "select linkman,company,companyaddr,companypostcode,giveqty,givelvl,dbo.getbignamebyid(inman) as giveman from vw_givelist where status=4 and giveedate>='" + cdate + "' and givemag like '%" + magname + "%' and givelvl='" + maglvl + "' and uid in (select uid from vw_linkman_key2) and inman in (select uid from t_user where mycompany='" + depts[0] + "' and mydept='" + depts[1] + "') order by linkman,company,companyaddr";
                    break;
            }
        }

        //string sSql = "select linkman as [name],company,companyaddr as address,companypostcode as postcode,giveqty as qty,givelvl as [level],dbo.getbignamebyid(inman) as postman from vw_givelist where status=4 and giveedate>='" + cdate + "' and givemag like '%" + magname + "%' order by givelvl";
        //string sSql = "select linkman as [name],company,companyaddr as address,companypostcode as postcode,giveqty as qty,givelvl as [level],dbo.getbignamebyid(inman) as postman from vw_givelist where status=4 and givebdate<='" + cdate + "' and giveedate>='" + cdate + "' and givemag like '%" + magname + "%' order by givelvl";
        //---
        TSqlDB.ExecuteNonQuery(myrowset+sSql);
        //---
        xlsfile.InnerHtml = "<a href=\"..\\makefiles\\" + myfilename + "\">下载结果文件</a>";
    }
}
